Statistics management for database querying

ABSTRACT

Methods and systems are provided for querying a database. One exemplary method for obtaining data from an on-demand database supporting one or more tenants involves obtaining model database statistics based on expected utilization information for a tenant and providing the model database statistics to the on-demand database. The on-demand database utilizes the model database statistics to generate a query plan and executes the query plan to obtain data from the model database statistics.

CROSS-REFERENCE TO RELATED APPLICATION(S)

This application claims the benefit of U.S. provisional patentapplication Ser. No. 61/499,304, filed Jun. 21, 2011, the entire contentof which is incorporated by reference herein.

TECHNICAL FIELD

Embodiments of the subject matter described herein relate generally tocomputer systems and networks configured to support applicationsexecuting on behalf of multiple tenants, and more particularly,embodiments of the subject matter relate to methods and systems forefficiently querying a multi-tenant database.

BACKGROUND

Modern software development is evolving away from the client-servermodel toward network-based processing systems that provide access todata and services via the Internet or other networks. In contrast totraditional systems that host networked applications on dedicated serverhardware, a “cloud” computing model allows applications to be providedover the network “as a service” supplied by an infrastructure provider.The infrastructure provider typically abstracts the underlying hardwareand other resources used to deliver a customer-developed application sothat the customer no longer needs to operate and support dedicatedserver hardware. The cloud computing model can often provide substantialcost savings to the customer over the life of the application becausethe customer no longer needs to provide dedicated networkinfrastructure, electrical and temperature controls, physical securityand other logistics in support of dedicated server hardware.

Multi-tenant cloud-based architectures have been developed to improvecollaboration, integration, and community-based cooperation betweencustomer tenants without sacrificing data security. Generally speaking,multi-tenancy refers to a system where a single hardware and softwareplatform simultaneously supports multiple user groups (also referred toas “organizations” or “tenants”) from a common data storage element(also referred to as a “multi-tenant database”). The multi-tenant designprovides a number of advantages over conventional server virtualizationsystems. First, the multi-tenant platform operator can often makeimprovements to the platform based upon collective information from theentire tenant community. Additionally, because all users in themulti-tenant environment execute applications within a common processingspace, it is relatively easy to grant or deny access to specific sets ofdata for any user within the multi-tenant platform, thereby improvingcollaboration and integration between applications and the data managedby the various applications. The multi-tenant architecture thereforeallows convenient and cost effective sharing of similar applicationfeatures between multiple sets of users.

During operation, there are numerous situations in which data and/orinformation needs to be retrieved from the multi-tenant database (e.g.,for presentation to users). Most databases include management softwarethat determines an optimal query plan for executing the query andretrieving the desired set of data and/or information. However, fordynamic multi-tenant databases, the database statistics that maintainedby the database management software and utilized to determine theoptimal query plan may not reflect the current state of the multi-tenantdatabase, and therefore, may result in suboptimal query plans that arenot the most efficient method of querying the multi-tenant database andmay result in timeouts or other undesirable effects.

BRIEF DESCRIPTION OF THE DRAWINGS

A more complete understanding of the subject matter may be derived byreferring to the detailed description and claims when considered inconjunction with the following figures, wherein like reference numbersrefer to similar elements throughout the figures.

FIG. 1 is a block diagram of an exemplary multi-tenant system;

FIG. 2 is a block diagram of an exemplary database statistics managementsystem suitable for use with the multi-tenant system of FIG. 1 inaccordance with one or more embodiments; and

FIG. 3 is a flow diagram of an exemplary database statistics managementprocess that may be performed by the multi-tenant system of FIG. 1and/or the database statistics management system of FIG. 2 in accordancewith one or more exemplary embodiments.

DETAILED DESCRIPTION

Embodiments of the subject matter described herein generally relate toefficiently querying on-demand multi-tenant databases by modifying thestatistics maintained by the database to reflect the expected (oranticipated) utilization of the database. As described in greater detailbelow, a development database representative of the on-demandmulti-tenant database is created and then modified based upon expectedutilization information for the tenants supported by the multi-tenantdatabase. In this regard, the tables and/or data entries of thedevelopment database are modified to reflect the expected (oranticipated) state of the on-demand multi-tenant database. Aftermodifying the development database, database statistics are determinedfor the modified development database, and a relevant subset of thosedatabase statistics are used to generate model database statistics. Asdescribed in greater detail below, the model database statistics arebased on or otherwise representative of the relevant subset of thedevelopment database statistics and/or a subset of the expectedutilization information, and as such, are representative of the expected(or anticipated) state of the on-demand multi-tenant database. After themodel database statistics are obtained, the statistics that aremaintained and utilized by the on-demand multi-tenant database togenerate query plans are modified to reflect the model databasestatistics. Thus, the model database statistics influence the queryplans generated and/or executed by the on-demand multi-tenant database.In one or more embodiments, the model database statistics are maintainedby a development server in an Extensible Markup Language (XML) formattedfile, and an application server accesses or otherwise receives the XMLfile and updates the database statistics maintained by the on-demandmulti-tenant database to reflect changes to the XML file, as describedin greater detail below.

Turning now to FIG. 1, an exemplary multi-tenant system 100 includes aserver 102 that dynamically creates and supports virtual applications128 based upon data 132 from a common database 130 that is sharedbetween multiple tenants, alternatively referred to herein as amulti-tenant database. Data and services generated by the virtualapplications 128 are provided via a network 145 to any number of clientdevices 140, as desired. Each virtual application 128 is suitablygenerated at run-time (or on-demand) using a common application platform110 that securely provides access to the data 132 in the database 130for each of the various tenants subscribing to the multi-tenant system100. In accordance with one non-limiting example, the multi-tenantsystem 100 is implemented in the form of an on-demand multi-tenantcustomer relationship management (CRM) system that can support anynumber of authenticated users of multiple tenants.

As used herein, a “tenant” or an “organization” should be understood asreferring to a group of one or more users that shares access to commonsubset of the data within the multi-tenant database 130. In this regard,each tenant includes one or more users associated with, assigned to, orotherwise belonging to that respective tenant. To put it another way,each respective user within the multi-tenant system 100 is associatedwith, assigned to, or otherwise belongs to a particular tenant of theplurality of tenants supported by the multi-tenant system 100. Tenantsmay represent customers, customer departments, business or legalorganizations, and/or any other entities that maintain data forparticular sets of users within the multi-tenant system 100. Althoughmultiple tenants may share access to the server 102 and the database130, the particular data and services provided from the server 102 toeach tenant can be securely isolated from those provided to othertenants. The multi-tenant architecture therefore allows different setsof users to share functionality and hardware resources withoutnecessarily sharing any of the data 132 belonging to or otherwiseassociated with other tenants.

The multi-tenant database 130 is any sort of repository or other datastorage system capable of storing and managing the data 132 associatedwith any number of tenants. The database 130 may be implemented usingany type of conventional database server hardware. In variousembodiments, the database 130 shares processing hardware 104 with theserver 102. In other embodiments, the database 130 is implemented usingseparate physical and/or virtual database server hardware thatcommunicates with the server 102 to perform the various functionsdescribed herein. In an exemplary embodiment, the database 130 includesa database management system or other equivalent software capable ofdetermining an optimal query plan for retrieving and providing aparticular subset of the data 132 to an instance of virtual application128 in response to a query initiated or otherwise provided by a virtualapplication 128, as described in greater detail below. The multi-tenantdatabase 130 may alternatively be referred to herein as an on-demanddatabase, in that the multi-tenant database 130 provides (or isavailable to provide) data at run-time to on-demand virtual applications128 generated by the application platform 110, as described in greaterdetail below.

In practice, the data 132 may be organized and formatted in any mannerto support the application platform 110. In various embodiments, thedata 132 is suitably organized into a relatively small number of largedata tables to maintain a semi-amorphous “heap”-type format. The data132 can then be organized as needed for a particular virtual application128. In various embodiments, conventional data relationships areestablished using any number of pivot tables 134 that establishindexing, uniqueness, relationships between entities, and/or otheraspects of conventional database organization as desired. Further datamanipulation and report formatting is generally performed at run-timeusing a variety of metadata constructs. Metadata within a universal datadirectory (UDD) 136, for example, can be used to describe any number offorms, reports, workflows, user access privileges, business logic andother constructs that are common to multiple tenants. Tenant-specificformatting, functions and other constructs may be maintained astenant-specific metadata 138 for each tenant, as desired. Rather thanforcing the data 132 into an inflexible global structure that is commonto all tenants and applications, the database 130 is organized to berelatively amorphous, with the pivot tables 134 and the metadata 138providing additional structure on an as-needed basis. To that end, theapplication platform 110 suitably uses the pivot tables 134 and/or themetadata 138 to generate “virtual” components of the virtualapplications 128 to logically obtain, process, and present therelatively amorphous data 132 from the database 130.

The server 102 is implemented using one or more actual and/or virtualcomputing systems that collectively provide the dynamic applicationplatform 110 for generating the virtual applications 128. For example,the server 102 may be implemented using a cluster of actual and/orvirtual servers operating in conjunction with each other, typically inassociation with conventional network communications, clustermanagement, load balancing and other features as appropriate. The server102 operates with any sort of conventional processing hardware 104, suchas a processor 105, memory 106, input/output features 107 and the like.The input/output features 107 generally represent the interface(s) tonetworks (e.g., to the network 145, or any other local area, wide areaor other network), mass storage, display devices, data entry devicesand/or the like. The processor 105 may be implemented using any suitableprocessing system, such as one or more processors, controllers,microprocessors, microcontrollers, processing cores and/or othercomputing resources spread across any number of distributed orintegrated systems, including any number of “cloud-based” or othervirtual systems. The memory 106 represents any non-transitory short orlong term storage or other computer-readable media capable of storingprogramming instructions for execution on the processor 105, includingany sort of random access memory (RAM), read only memory (ROM), flashmemory, magnetic or optical mass storage, and/or the like. Thecomputer-executable programming instructions, when read and executed bythe server 102 and/or processor 105, cause the server 102 and/orprocessor 105 to create, generate, or otherwise facilitate theapplication platform 110 and/or virtual applications 128 and perform oneor more additional tasks, operations, functions, and/or processesdescribed herein. It should be noted that the memory 106 represents onesuitable implementation of such computer-readable media, andalternatively or additionally, the server 102 could receive andcooperate with external computer-readable media that is realized as aportable or mobile component or platform, e.g., a portable hard drive, aUSB flash drive, an optical disc, or the like.

The application platform 110 is any sort of software application orother data processing engine that generates the virtual applications 128that provide data and/or services to the client devices 140. In atypical embodiment, the application platform 110 gains access toprocessing resources, communications interfaces and other features ofthe processing hardware 104 using any sort of conventional orproprietary operating system 108. The virtual applications 128 aretypically generated at run-time in response to input received from theclient devices 140. For the illustrated embodiment, the applicationplatform 110 includes a bulk data processing engine 112, a querygenerator 114, a search engine 116 that provides text indexing and othersearch functionality, and a runtime application generator 120. Each ofthese features may be implemented as a separate process or other module,and many equivalent embodiments could include different and/oradditional features, components or other modules as desired.

The runtime application generator 120 dynamically builds and executesthe virtual applications 128 in response to specific requests receivedfrom the client devices 140. The virtual applications 128 are typicallyconstructed in accordance with the tenant-specific metadata 138, whichdescribes the particular tables, reports, interfaces and/or otherfeatures of the particular application 128. In various embodiments, eachvirtual application 128 generates dynamic web content that can be servedto a browser or other client program 142 associated with its clientdevice 140, as appropriate.

The runtime application generator 120 suitably interacts with the querygenerator 114 to efficiently obtain multi-tenant data 132 from thedatabase 130 as needed in response to input queries initiated orotherwise provided by users of the client devices 140. In a typicalembodiment, the query generator 114 considers the identity of the userrequesting a particular function (along with the user's associatedtenant), and then builds and executes queries to the database 130 usingsystem-wide metadata 136, tenant specific metadata 138, pivot tables134, and/or any other available resources. The query generator 114 inthis example therefore maintains security of the common database 130 byensuring that queries are consistent with access privileges granted tothe user and/or tenant that initiated the request.

Still referring to FIG. 1, the data processing engine 112 performs bulkprocessing operations on the data 132 such as uploads or downloads,updates, online transaction processing, and/or the like. In manyembodiments, less urgent bulk processing of the data 132 can bescheduled to occur as processing resources become available, therebygiving priority to more urgent data processing by the query generator114, the search engine 116, the virtual applications 128, etc.

In exemplary embodiments, the application platform 110 is utilized tocreate and/or generate data-driven virtual applications 128 for thetenants that they support. Such virtual applications 128 may make use ofinterface features such as custom (or tenant-specific) screens 124,standard (or universal) screens 122 or the like. Any number of customand/or standard objects 126 may also be available for integration intotenant-developed virtual applications 128. As used herein, “custom”should be understood as meaning that a respective object or applicationis tenant-specific (e.g., only available to users associated with aparticular tenant in the multi-tenant system) or user-specific (e.g.,only available to a particular subset of users within the multi-tenantsystem), whereas “standard” or “universal” applications or objects areavailable across multiple tenants in the multi-tenant system. The data132 associated with each virtual application 128 is provided to thedatabase 130, as appropriate, and stored until it is requested or isotherwise needed, along with the metadata 138 that describes theparticular features (e.g., reports, tables, functions, objects, fields,formulas, code, etc.) of that particular virtual application 128. Forexample, a virtual application 128 may include a number of objects 126accessible to a tenant, wherein for each object 126 accessible to thetenant, information pertaining to its object type along with values forvarious fields associated with that respective object type aremaintained as metadata 138 in the database 130. In this regard, theobject type defines the structure (e.g., the formatting, functions andother constructs) of each respective object 126 and the various fieldsassociated therewith.

Still referring to FIG. 1, the data and services provided by the server102 can be retrieved using any sort of personal computer, mobiletelephone, tablet or other network-enabled client device 140 on thenetwork 145. In an exemplary embodiment, the client device 140 includesa display device, such as a monitor, screen, or another conventionalelectronic display capable of graphically presenting data and/orinformation retrieved from the multi-tenant database 130, as describedin greater detail below. Typically, the user operates a conventionalbrowser application or other client program 142 executed by the clientdevice 140 to contact the server 102 via the network 145 using anetworking protocol, such as the hypertext transport protocol (HTTP) orthe like. The user typically authenticates his or her identity to theserver 102 to obtain a session identifier (“SessionID”) that identifiesthe user in subsequent communications with the server 102. When theidentified user requests access to a virtual application 128, theruntime application generator 120 suitably creates the application atrun time based upon the metadata 138, as appropriate. As noted above,the virtual application 128 may contain Java, ActiveX, or other contentthat can be presented using conventional client software running on theclient device 140; other embodiments may simply provide dynamic web orother content that can be presented and viewed by the user, as desired.As described in greater detail below, the query generator 114 suitablyobtains the requested subsets of data 132 from the database 130 asneeded to populate the tables, reports or other features of theparticular virtual application 128.

FIG. 2 illustrates an exemplary database statistics management system200 suitable for use with a multi-tenant computing system, such as themulti-tenant system 100. The illustrated database statistics managementsystem 200 includes an application server 202 (e.g., server 102), amulti-tenant database 204 (e.g., database 130), a development server206, and a development database 208. Some elements in the databasestatistics management system 200 of FIG. 2 are similar to counterpartelements described above in the context of multi-tenant system 100 ofFIG. 1, and such common features and/or functionality will not beredundantly described in the context of FIG. 2. In this regard, theapplication server 202 is coupled to the multi-tenant database 204 andgenerates or otherwise supports an application platform 210 (e.g.,application platform 120) to provide virtual applications (e.g., virtualapplications 128) to users and/or tenants in a multi-tenant system, asdescribed above in the context of FIG. 1. It should be understood thatFIG. 2 is a simplified representation of the database statisticsmanagement system 200 for purposes of explanation and is not intended tolimit the subject matter described herein in any way. For example,although not illustrated in FIG. 2, in practice, the development server206 includes processing hardware (e.g., processing hardware 104)configured to support operation of the database statistics managementsystem 200 and the various database statistics management tasks andprocesses described herein.

In the illustrated embodiment, the multi-tenant database 204 maintainsdata for the various tenants (e.g., data 132) in a plurality ofdifferent tables 220. In this regard, the data maintained by themulti-tenant database 204 may be understood as on-demand data that isaccessible to tenants and/or users in a multi-tenant system and capableof being dynamically added, deleted, or otherwise modified in real-timeby its associated tenant and/or user via a virtual application generatedby the application platform 210. Each table 220 contains a subset of thedata maintained by the multi-tenant database 204 and includes a numberof rows corresponding to the number of entries in that respective table220 and a number of columns across the rows that correspond to thedifferent pieces of data and/or information maintained in thatrespective table 220. By virtue of the multi-tenant database 204 beingpart of an on-demand multi-tenant system, the number of entries in arespective table 220 in the multi-tenant database 204 may varydynamically in response to tenants and/or users the multi-tenant systemadding and/or deleting data entries.

In an exemplary embodiment, the multi-tenant database 204 includes adatabase management system 222 that provides an interface between thedata maintained by the multi-tenant database 204 (e.g., in tables 220)and the application server 202 and/or other external elements. Thedatabase management system 222 generally represents the software moduleof the multi-tenant database 204 that receives queries from theapplication platform 210 (e.g., from query generator 114) and generatesor otherwise supports a query optimizer 224 that determines an optimalquery plan (e.g., join orders, join methods, access paths, key indexes,and the like) for performing a received query based on databasestatistics 226 maintained by the database management system 222. Thedatabase statistics 226 include information pertaining to the contentand/or arrangement of the data maintained in the tables 220 of themulti-tenant database 204, such as, for example, the number of rowsand/or columns of a particular table 220, the number of differentdistinct values for each column of a particular table 220, schemainformation and/or other relational information for the various tables220. The database management system 222 executes or otherwise performsthe received query in accordance with the query plan determined by thequery optimizer 224 to retrieve the desired subset of the datamaintained in the tables 220 and/or multi-tenant database 204, and thedatabase management system 222 provides the retrieved data to theapplication server 202 and/or the application platform 210 (e.g., thequery generator and/or the querying virtual application) as the resultof the query. The database statistics 226 may alternatively be referredto herein as on-demand database statistics because the databasestatistics 226 influence the query plans determined and/or executed bythe database management system 222 and/or query optimizer 224 to supportthe virtual applications generated and/or provided by the applicationserver 202.

In some embodiments, upon creation of a new table in the multi-tenantdatabase 204, the database management system 222 automaticallydetermines initial statistics for the new table 220 (e.g., the number ofrows and/or columns, the number of different distinct values for eachcolumn, and the like) and updates the on-demand database statistics 226to include those statistics for the new table. However, due to thedynamic and multi-tenant nature of the tables 220 (and the datacontained therein), the initial statistics determined by the databasemanagement system 222 for one or more tables 220 of the multi-tenantdatabase 204 may become inaccurate over time, which may, in turn, resultin the query optimizer 224 generating suboptimal query plans. Asdescribed in greater detail below, rather than the database managementsystem 222 continuously and/or periodically re-determining statisticsfor the multi-tenant database 204, the application server 202 and/orapplication platform 210 updates or otherwise modifies the on-demanddatabase statistics 226 maintained by the database management system 222in the multi-tenant database 204 to contain values and/or criteria forthe statistics of the various tables 220 (e.g., the number of rowsand/or columns, the number of different distinct values for each column,and the like) that provide a more accurate representation of thereal-time data in the multi-tenant database 204. Thus, processingresources of the multi-tenant database 204 and/or the databasemanagement system 222 which would otherwise be allocated to determiningstatistics for the multi-tenant database 204 are available to handlequeries from the application server 202.

Still referring to FIG. 2, in exemplary embodiments, the developmentdatabase 208 maintains data in a plurality of different tables 230 thatis representative of the real-time data likely to be maintained by themulti-tenant database 204. In this regard, the data maintained by thedevelopment database 208 may be instantiated by copying the data and/ortables 220 maintained by the multi-tenant database 204. For example, abackup of the multi-tenant database 204 may be obtained using built-inbackup/restore functionality of the database management system 222, andthe backup of the multi-tenant database 204 may then be restored intothe development database 208 using the built-in backup/restorefunctionality of the database management system 232 of the developmentdatabase 208. In some embodiments, the database management system 232executes a post-processing script to delete or otherwise removepotentially sensitive and/or confidential information from thedevelopment database 208 after restoring the backup of the multi-tenantdatabase 204 to the development database 208. While the data maintainedby the multi-tenant database 204 is available on-demand to tenantsand/or users in a multi-tenant system, the data maintained by thedevelopment database 208 is not available to tenants and/or users in amulti-tenant system. As described above in the context of themulti-tenant database 204, the database management system 232 providesan interface to the data maintained by the development database 208(e.g., in tables 230) and determines database statistics 234 for thedevelopment database 208 based on the data maintained in the tables 230.

In exemplary embodiments, the development server 206 is coupled to thedevelopment database 208 and generates, executes, or otherwise supportsa development application 240 that interacts with the databasemanagement system 232 to modify the data and/or the tables 230maintained by the development database 208 based on expected utilizationinformation for one or more of the tables 220 in the multi-tenantdatabase 204 and generate model database statistics 242 for themulti-tenant database 204 that are influenced by the expectedutilization information and/or based on the modified developmentdatabase 208. Depending on the embodiment, the expected utilizationinformation for a particular table 220 in the multi-tenant database 204may be provided by one or more tenants having data maintained in thattable 220 or the development application 240 may predict or otherwisedetermine the expected utilization information, as described in greaterdetail below. In an exemplary embodiment, the development application240 maintains the model database statistics 242 in an XML formattedfile. The development server 206 is communicatively coupled to theapplication server 202 via a network 205 (e.g., network 145), and themodel database statistics 242 are provided to and/or otherwise accessedby the application platform 210 on the application server 202. Asdescribed in greater detail below, the application platform 210 and/orapplication server 202 maintains an XML file of reference databasestatistics 212, wherein in response to identifying a difference betweenthe model database statistics 242 and the updated reference databasestatistics 212, the application platform 210 and/or application server202 updates the updated reference database statistics 212 maintained onthe application server 202 to match the model database statistics 242provided by the development server 206. Additionally, after updating thereference database statistics 212 to reflect changes to the modeldatabase statistics 242, the application platform 210 and/or applicationserver 202 modifies or otherwise updates a subset of the on-demanddatabase statistics 226 to match the updated reference databasestatistics 212 maintained by the application server 202. In this manner,the updated on-demand database statistics 226 are representative of thedata expected (or anticipated) to be maintained by the multi-tenantdatabase 204, and thus, provide a more accurate representation of thereal-time data in the multi-tenant database 204 than the statisticsdetermined by the database management system 222 upon creation of thetables 220 as the content of the multi-tenant database 204 varies overtime.

FIG. 3 depicts an exemplary embodiment of a database statisticsmanagement process 300 suitable for implementation in a multi-tenantcomputing system to obtain data and/or information from an on-demandmulti-tenant database in an efficient manner. The various tasksperformed in connection with the illustrated process 300 may beperformed by software, hardware, firmware, or any combination thereofFor illustrative purposes, the following description may refer toelements mentioned above in connection with FIGS. 1-2. In practice,portions of the database statistics management process 300 may beperformed by different elements of the multi-tenant system 100 and/orthe database statistics management system 200, such as, for example, theapplication server 102, 202, the application platform 110, 210, themulti-tenant database 130, 204, the development server 206, thedevelopment application 240, and/or the database management systems 222,232. It should be appreciated that the database statistics managementprocess 300 may include any number of additional or alternative tasks,the tasks need not be performed in the illustrated order and/or thetasks may be performed concurrently, and/or the database statisticsmanagement process 300 may be incorporated into a more comprehensiveprocedure or process having additional functionality not described indetail herein. Moreover, one or more of the tasks shown and described inthe context of FIG. 3 could be omitted from a practical embodiment ofthe database statistics management process 300 as long as the intendedoverall functionality remains intact.

Referring to FIG. 3, and with continued reference to FIGS. 1-2, in anexemplary embodiment, the database statistics management process 300begins by creating or otherwise generating a development databaserepresentative of the on-demand multi-tenant database (task 302). Inthis regard, the development database is instantiated so that theinitial contents of the development database are substantially identicalto the contents of the multi-tenant database. For example, in accordancewith one or more embodiments, the development database 208 isinstantiated by copying the tables 220 of the multi-tenant database 204to the development database 208 so that the initial content of thetables 230 is identical to the content of the tables 220. To put itanother way, each of the initial tables 230 of the development database208 correspond to the current tables 220 of the multi-tenant database204 when the development database 208 is created or otherwiseinstantiated.

After a development database representative of the on-demandmulti-tenant database is created, the database statistics managementprocess 300 continues by obtaining, for one or more of the tenantssupported by the on-demand multi-tenant database, expected (oranticipated) utilization information for one or more tables of theon-demand multi-tenant database (task 304). In this regard, the expectedutilization information represents a particular tenant's likely usage ofa particular table at a particular time in the future. The expectedutilization information may include the expected amount of data entries(or rows) for the particular table of interest along with the expectednumber of distinct values for one or more columns of that particulartable, minimum and/or maximum values for one or more columns of theparticular table, the faction (or percentage) of values in a particularcolumn that are null, the number of data blocks in a particular table orindex, the depth of the B-tree in a particular index, and the clusteringfactor (i.e., how closely the ordering of an index corresponds to thephysical ordering of the data in the corresponding table) of aparticular index, and the like. Depending on the embodiment, theexpected utilization information may pertain to an existing table in themulti-tenant database or a future (or new) table to be created in themulti-tenant database. In accordance with one or more embodiments, thedevelopment application 240 receives the expected utilizationinformation from the individual tenants supported by the multi-tenantdatabase 204. For example, the application platform 110, 210 may prompta user belonging to a particular tenant (e.g., via a virtual application128 presented on a client device 140) to provide the expectedutilization information for a new or existing table in the multi-tenantdatabase 204 and transmit the expected utilization information providedby the user to the development application 240. In other embodiments,the development application 240 may calculate or otherwise determine theexpected utilization information for a tenant based on that tenant'scurrent and/or past utilization of one or more tables of themulti-tenant database 204. For example, for an existing table, thedevelopment application 240 may analyze a tenant's current and/or pastutilization of the table, and based on that utilization history,estimate or otherwise project the expected utilization information at aparticular time in the future (e.g., using various projectionalgorithms, models and/or metrics). Similarly, for a new table to becreated in the multi-tenant database 204, the development application240 may analyze a tenant's utilization information for another similartable, and based on that prior utilization information, project theexpected utilization information for the new table.

Still referring to FIG. 3, in an exemplary embodiment, the databasestatistics management process 300 continues by modifying the developmentdatabase based on the expected utilization information (task 306). Inthis regard, the development application 240 modifies the content of oneor more tables 230 of the development database 208 to reflect theexpected utilization for those corresponding tables 220 of themulti-tenant database 204. For example, if the expected utilizationinformation for a first tenant indicates that the first tenant is likelyto have a different number of data entries in a particular table 221 ofthe multi-tenant database 204 in the future relative to the firsttenant's current number of entries in the table 221, the developmentapplication 240 creates additional entries and/or deletes existingentries for the first tenant in the table 231 of the developmentdatabase 208 that corresponds to that table 221 of the multi-tenantdatabase 204 so that the number of data entries for the first tenant inthe table 231 is equal to the expected number of data entries for thefirst tenant. In some embodiments, the development application 240 maymodify the content of the data entries for the first tenant in the table231 to have the expected number of distinct values for that column ofthe table 221, the expected minimum and/or maximum values for thatcolumn of the table 221, or the like. Likewise, if the expectedutilization information for a second tenant indicates that the secondtenant is likely to have a different number of data entries in thattable 221 than the second tenant's current number of entries in thetable 221, the development application 240 creates additional entriesand/or deletes existing entries for the second tenant in the table 231for the second tenant in the table 231 of the development database 208so that the number of data entries for the second tenant in the table231 is equal to the expected number of data entries for the secondtenant. Additionally, the development application 240 may modify thecontent of the data entries for the second tenant in the table 231 tohave the expected values (or range thereof) indicated by the secondtenant's expected utilization information.

For each respective table 220 in the multi-tenant database 204, thedevelopment application 240 may modify the corresponding table 230 inthe development database 208 to reflect the expected utilizationinformation for that respective table 220 that was obtained for each ofthe tenants that utilizes that respective table 220. For a new (orfuture) table that has not been created in the multi-tenant database204, the development application 240 interacts with the databasemanagement system 232 to create the new table in the developmentdatabase 208 that reflects the expected utilization information for thatnew table. For example, if the expected utilization information for afirst tenant indicates that the first tenant is likely to have onehundred different data entries in the new table with a certain number ofdistinct values and/or a range of values for a first column of the newtable, the development application 240 creates the new table in thedevelopment database 208 having one hundred rows for the first tenant,wherein values for the first column across those one hundred rows have arange and/or a number of distinct values corresponding to the expectedvalues for the first tenant. Likewise, if the expected utilizationinformation for a second tenant indicates that the second tenant islikely to have ten different data entries in that new table, thedevelopment application 240 creates ten rows in the new table of thedevelopment database 208 wherein values for the first column acrossthose ten rows correspond to the second tenant's expected utilizationinformation. In this manner, the development application 240 modifiesthe development database 208 based on the expected utilizationinformation for the various existing and/or future tables of themulti-tenant database 204 for the various tenants supported by themulti-tenant database 204 so that the tables 230 in the developmentdatabase 208 reflect the anticipated future state of the multi-tenantdatabase 204 and/or tables 220 at some point in the future.

In an exemplary embodiment, the database statistics management process300 continues by generating or otherwise obtaining model databasestatistics for the on-demand multi-tenant database based on the modifieddevelopment database and the expected utilization information (task308). In this regard, after modifying the tables 230 of the developmentdatabase 208, the development application 240 signals, instructs, orotherwise commands the database management system 232 to determinedatabase statistics 234 for the development database 208. After thedatabase management system 232 determines the modified developmentdatabase statistics 234, the development application 240 queries thedatabase management system 232 to obtain, from the database managementsystem 232, a subset of the development database statistics 234 that aremost likely to influence the query optimizer 224. For example, for aparticular table 231, the development application 240 may query thedatabase management system 232 for the number of rows in a particulartable 230, the number of distinct values for a particular column of aparticular table 230, the number of distinct keys for an index of aparticular table 230, or the like. In this regard, by requesting themost relevant subset of the development database statistics 234 from thedatabase management system 232, the development application 240effectively filters or otherwise excludes the remaining developmentdatabase statistics 234 that are unlikely to influence the queryoptimizer 224 from the model database statistics. Based on the relevantsubset of the development database statistics 234 obtained from thedevelopment database 208 and/or the expected utilization informationobtained by the development application 240, the development application240 generates model database statistics 242 consisting of the statisticsthat are likely to influence the query optimizer 224 and having valuesrepresentative of the anticipated state of the multi-tenant database204. In this regard, when the development application 240 does notmodify the contents of the tables 230 to reflect the expected values forthe various columns of the tables 220, the development application 240utilizes the expected utilization information (e.g., expected number ofdistinct values for particular columns of the tables 220, expectedminimum and/or maximum values for particular columns of the tables 220,or the like) to augment the relevant subset of the development databasestatistics 234 and create the model database statistics 242. In anexemplary embodiment, the model database statistics 242 are maintainedin an XML file that is created by the development application 240 andmaintained by the development server 206.

After generating the model database statistics based on the modifieddevelopment database and the expected utilization information, the modeldatabase statistics are provided to the on-demand multi-tenant databaseby modifying or otherwise updating the on-demand database statisticsmaintained by the on-demand multi-tenant database to reflect the modeldatabase statistics (task 310). In an exemplary embodiment, theapplication platform 210 receives or otherwise accesses the XML file onthe development server 206 that contains the model database statistics242 and compares the model database statistics 242 to the referencedatabase statistics 212 maintained by the application server 202. Inresponse to identifying a difference between the reference databasestatistics 212 and the model database statistics 242, the applicationplatform 210 modifies the reference database statistics 212 to reflectthe model database statistics 242. In some embodiments, the applicationplatform 210 parses the XML files for the two sets of databasestatistics 212, 242 overwrites portions of the reference databasestatistics 212 that differ from the model database statistics 242 withthe values from the model database statistics XML file. In otherembodiments, the application platform 210 downloads or otherwise copiesthe XML file for the model database statistics 242 from the developmentserver 206 and overwrites the previous XML file for the referencedatabase statistics 212 with the downloaded XML file.

After updating the reference database statistics 212, the applicationplatform 210 automatically updates the on-demand database statistics 226to reflect the updated reference database statistics 212, and thereby,reflect the model database statistics 242. In this regard, theapplication platform 210 interacts with the database management system222 to overwrite portions of the on-demand database statistics 226(e.g., the relevant subset of the on-demand database statistics) withvalues and/or criteria from the updated reference database statistics212 so that portions of the on-demand database statistics 226 correspondto the model database statistics 242. In other words, the applicationplatform 210 modifies the relevant subset of the on-demand databasestatistics 226 to have values and/or criteria corresponding to thoseprovided by the model database statistics 242. Thus, the updatedon-demand database statistics 226 include values and/or criteria for oneor more statistics that may not be representative of the current (orinstantaneous) state of the multi-tenant database 204, but rather,reflect the expected (or anticipated) state of the multi-tenant database204 at some point in the future. For example, as described above, thedatabase management system 222 may initially determine referencedatabase statistics for a first table 221 automatically in response tocreating the first table 221 in the multi-tenant database 204. Theapplication platform 210 modifies the subset of the on-demand databasestatistics 226 corresponding to the first table 221 with values and/orcriteria determined by the database management system 232 and/or thedevelopment application 240 for those statistics based on the modifiedfirst table 231 that reflects the expected utilization of the firsttable 221 and/or other expected utilization information for the firsttable 221. Thus, the updated on-demand database statistics 226 for thefirst table 221 account for changes in the number of entries in thefirst table 221 and/or the content of the first table 221 that occurafter the database management system 222 determines the initialreference database statistics for the first table 221.

After the database statistics maintained by the on-demand multi-tenantdatabase are updated to reflect the model database statistics determinedbased on the development database, the database statistics managementprocess 300 continues by utilizing those updated database statistics forsubsequent queries of the on-demand multi-tenant database (task 312). Inthis regard, virtual applications 128 generated or otherwise executed bythe application platform 110, 210 produce queries that are provided tothe query generator 114, which, in turn, constructs query statements(e.g., in SQL or another suitable database querying language) that areprovided by the application platform 110, 210 to the database managementsystem 222. In response to a query statement, the query optimizer 224utilizes the on-demand database statistics 226 to determine a pluralityof possible query plans (e.g., combinations and/or sequences of accesspaths, join operations, join orders, tables and/or indexes, and thelike) based on the query statement and the on-demand database statistics226 and identifies the possible query plan having the lowest estimatedcost (e.g., based on required computing resources, amount of computingtime required, and the like) as the optimal query plan. In this manner,the query plans determined by the query optimizer 224 are influenced bythe model database statistics 242 which are represented by a subset ofthe on-demand database statistics 226. After the query optimizer 224determines the optimal query plan, the database management system 222executes or otherwise performs the query using that query plan to obtainthe results of the query statement from the tables 220 of themulti-tenant database 204. The resultant data retrieved from themulti-tenant database 204 is then provided to the querying instance ofthe virtual application 128 via the application platform 110, 210. Inthis regard, by virtue of the relevant subset of the on-demand databasestatistics 226 being updated to reflect the expected (or anticipated)state of the multi-tenant database 204, the query optimizer 224 iscapable of determining efficient query plans without requiring thedatabase management system 222 to continuously update and/orre-determine the on-demand database statistics 226 as the size of thetables 220 and/or the contents of the on-demand multi-tenant database204 dynamically change over time. Thus, timeouts can be reduced withoutundesirably consuming processing resources of the database managementsystem 222 and/or multi-tenant database 204 to re-determine statisticsfor the on-demand multi-tenant database 204 while the on-demandmulti-tenant database 204 is being accessed concurrently by users and/ortenants.

In exemplary embodiments, the loop defined by tasks 302, 304, 306, 308,310 and 312 may repeat as desired throughout operation of themulti-tenant system. In this regard, the development database 208 may beperiodically updated or modified to reflect the current state of themulti-tenant database 204 (e.g., by periodically copying the tables 220to the development database 208) and account for variations between theactual utilization of the tables 220 of the multi-tenant database 204and the previously expected utilization information (e.g., task 302).Similarly, the expected utilization information may be periodicallyupdated and/or re-determined to account for changes in the usage byindividual tenants (e.g., task 304). The recently obtained expectedutilization information may then be used to modify the updateddevelopment database, generate updated model database statistics, andupdate the on-demand database statistics accordingly for use ingenerating query plans for subsequent queries of the on-demandmulti-tenant database (e.g., tasks 306, 308, 310, 312).

The foregoing description is merely illustrative in nature and is notintended to limit the embodiments of the subject matter or theapplication and uses of such embodiments. Furthermore, there is nointention to be bound by any expressed or implied theory presented inthe technical field, background, or the detailed description. As usedherein, the word “exemplary” means “serving as an example, instance, orillustration.” Any implementation described herein as exemplary is notnecessarily to be construed as preferred or advantageous over otherimplementations, and the exemplary embodiments described herein are notintended to limit the scope or applicability of the subject matter inany way.

For the sake of brevity, conventional techniques related to computerprogramming, computer networking, database querying, databasestatistics, query plan generation, XML and other functional aspects ofthe systems (and the individual operating components of the systems) maynot be described in detail herein. In addition, those skilled in the artwill appreciate that embodiments may be practiced in conjunction withany number of system and/or network architectures, data transmissionprotocols, and device configurations, and that the system describedherein is merely one suitable example. Furthermore, certain terminologymay be used herein for the purpose of reference only, and thus is notintended to be limiting. For example, the terms “first”, “second” andother such numerical terms do not imply a sequence or order unlessclearly indicated by the context.

Embodiments of the subject matter may be described herein in terms offunctional and/or logical block components, and with reference tosymbolic representations of operations, processing tasks, and functionsthat may be performed by various computing components or devices. Suchoperations, tasks, and functions are sometimes referred to as beingcomputer-executed, computerized, software-implemented, orcomputer-implemented. In this regard, it should be appreciated that thevarious block components shown in the figures may be realized by anynumber of hardware, software, and/or firmware components configured toperform the specified functions. For example, an embodiment of a systemor a component may employ various integrated circuit components, e.g.,memory elements, digital signal processing elements, logic elements,look-up tables, or the like, which may carry out a variety of functionsunder the control of one or more microprocessors or other controldevices. In this regard, the subject matter described herein can beimplemented in the context of any computer-implemented system and/or inconnection with two or more separate and distinct computer-implementedsystems that cooperate and communicate with one another. That said, inexemplary embodiments, the subject matter described herein isimplemented in conjunction with a virtual customer relationshipmanagement (CRM) application in a multi-tenant environment.

While at least one exemplary embodiment has been presented in theforegoing detailed description, it should be appreciated that a vastnumber of variations exist. It should also be appreciated that theexemplary embodiment or embodiments described herein are not intended tolimit the scope, applicability, or configuration of the claimed subjectmatter in any way. Rather, the foregoing detailed description willprovide those skilled in the art with a convenient road map forimplementing the described embodiment or embodiments. It should beunderstood that various changes can be made in the function andarrangement of elements without departing from the scope defined by theclaims, which includes known equivalents and foreseeable equivalents atthe time of filing this patent application. Accordingly, details of theexemplary embodiments or other limitations described above should not beread into the claims absent a clear intention to the contrary.

1. A method of obtaining data from a database, the method comprising:obtaining model database statistics for the database, the model databasestatistics being based on expected utilization information for a tenantsupported by the database; and providing the model database statisticsto the database, wherein a query plan generated by the database toobtain the data is influenced by the model database statistics.
 2. Themethod of claim 1, further comprising obtaining the expected utilizationinformation for the database from the tenant.
 3. The method of claim 1,further comprising determining the expected utilization information forthe database based at least in part on existing utilization informationfor the tenant.
 4. The method of claim 1, further comprising: creating adevelopment database representative of the database; and modifying thedevelopment database based on the expected utilization information,wherein obtaining the model database statistics comprises: aftermodifying the development database, generating the model databasestatistics based on the development database.
 5. The method of claim 4,wherein generating the model database statistics based on thedevelopment database comprises: obtaining, from the developmentdatabase, a subset of database statistics determined by a databasemanagement system of the development database after modifying thedevelopment database; and creating the model database statistics basedat least in part on the obtained subset of database statisticsdetermined by the database management system.
 6. The method of claim 5,wherein creating the model database statistics comprises creating anextensible markup language (XML) file including the obtained subset ofdatabase statistics determined by the database management system.
 7. Themethod of claim 6, further comprising: maintaining, by a developmentserver, the XML file including the obtained subset of databasestatistics determined by the database management system; andmaintaining, by an application server communicatively coupled to thedevelopment server, a second XML file containing reference databasestatistics for the database, wherein: obtaining the model databasestatistics comprises modifying, by the application server, the secondXML file to reflect the XML file maintained by the development server;and providing the model database statistics comprises automaticallymodifying, by the application server, database statistics maintained bythe database to reflect the second XML file after modifying the secondXML file, the database generating the query plan based on the databasestatistics maintained by the database.
 8. The method of claim 4, theexpected utilization information corresponding to a new table to becreated in the database, wherein modifying the development databasecomprises creating the new table in the development database.
 9. Themethod of claim 8, the expected utilization information including anexpected number of entries for the new table, wherein creating the newtable comprises creating the new table having the expected number ofentries.
 10. The method of claim 8, the expected utilization informationincluding expected value information for the new table, wherein creatingthe new table comprises creating an entry in the new table having anexpected value corresponding to the expected value information.
 11. Themethod of claim 1, the database maintaining reference databasestatistics for a first table of the database, wherein: obtaining themodel database statistics for the database comprises obtaining the modeldatabase statistics for the first table based on the expectedutilization information for the tenant for the first table; andproviding the model database statistics comprises modifying thereference database statistics to reflect the model database statistics.12. A method of querying an on-demand database in a multi-tenant systemincluding an application server providing instances of a virtualapplication over a network to a plurality of tenants, the methodcomprising: obtaining expected utilization information for the on-demanddatabase from one or more tenants of the plurality of tenants;obtaining, by the application server, model database statistics for theon-demand database, the model database statistics being based on theexpected utilization information; modifying, by the application server,database statistics maintained by the on-demand database to reflect themodel database statistics, resulting in updated database statisticsmaintained by the on-demand database; and providing, by the applicationserver, a query statement to the on-demand database, wherein theon-demand database determines a query plan based on the query statementand the updated database statistics.
 13. The method of claim 12, furthercomprising: instantiating a development database representative of theon-demand database; modifying the development database based on theexpected utilization information; and generating the model databasestatistics based on the development database after modifying thedevelopment database.
 14. The method of claim 13, the on-demand databaseincluding one or more tables containing data for the plurality oftenants, wherein instantiating the development database comprisescopying the one or more tables to the development database.
 15. Themethod of claim 14, the expected utilization information correspondingto an expected number of entries for a first table of the one or moretables of the on-demand database, wherein modifying the developmentdatabase comprises modifying the first table of the development databaseto have the expected number of entries.
 16. The method of claim 14, theexpected utilization information corresponding to an expected number ofentries for a future table in the on-demand database, wherein modifyingthe development database comprises creating a new table in thedevelopment database having the expected number of entries.
 17. Themethod of claim 13, the development database maintaining databasestatistics determined after modifying the development database, whereingenerating the model database statistics comprises: obtaining, from thedevelopment database, a subset of the database statistics determined bythe development database after modifying the development database; andcreating an extensible markup language (XML) file containing the modeldatabase statistics based at least in part on the obtained subset. 18.The method of claim 12, wherein the virtual application comprises acustomer relationship management (CRM) application.
 19. A computingsystem comprising a processor and a memory, wherein the memory comprisescomputer-executable instructions that, when executed by the processor,cause the computing system to: obtain model database statistics for anon-demand database, the model database statistics being based onexpected utilization information for one or more tenants having datamaintained in the on-demand database; provide the model databasestatistics to the on-demand database; and provide a query statement tothe on-demand database, wherein the on-demand database determines aquery plan based on the model database statistics and the querystatement.
 20. The computer system of claim 19, wherein thecomputer-executable instructions cause the computer system to modify adevelopment database representative of the on-demand database based onthe expected utilization information, the model database statisticsbeing generated based on the development database after modifying thedevelopment database.